﻿using CPFrameWork.UIInterface.Grid;
using System;
using System.Collections.Generic;
using System.Text;

namespace BusinessManage.DataSource.Grid
{
    public class ThreeGridSource : CPFrameWork.UIInterface.Grid.CPGridInterface
    {
        /// <summary>
        /// 列表数据，从数据库里读取出来后的扩展开发接口，在这个接口里，可以更改显示到列表界面上的数据
        /// </summary>
        /// <param name="e"></param>
        /// <returns></returns>
        public bool AfterReadDataFromDb(CPAfterReadDataFromDbEventArgs e)
        {
            //获取列表配置对旬 e.GridObj;
            //获取列表数据e.RealData;
            //修改某一行数据的值
            // e.RealData.Rows[0]["Name"] = "修改后的数据";
            return true;
        }


        public bool BeforeReadDataFromDb(CPBeforeReadDataFromDbEventArgs e)
        {
            //获取列表配置对旬 e.GridObj;
            //获取或设置列表取数据的SQL语句 ：e.StrSql
            // string querystr = "";
            DateTime startTime = DateTime.Now; DateTime endTime = DateTime.Now; string zqj = string.Empty; ;
            string queryStr = e.QueryCondition; string strWhere = string.Empty;
            if (string.IsNullOrEmpty(queryStr) || queryStr.Equals(" 1=2 "))
            {
                DateTime dt = DateTime.Now;  //当前时间  
                DateTime startMonth = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-01")).AddMonths(-1);//本月月初  
                DateTime endMonth = DateTime.Now;  //本月月末 
                zqj = startMonth.ToString("yyyyMM") + "-" + endMonth.ToString("yyyyMM");
                startTime = Convert.ToDateTime(startMonth.ToString("yyyy-MM") + "-01" + " 00:00:00");
                endTime = Convert.ToDateTime(endMonth.ToString("yyyy-MM") + "-01" + " 00:00:00");
                strWhere = "WaitDeliveryTime between  '" + startTime + "' and '" + endTime + "'";
            }
            else
            {
                string[] arraystring;
                if (queryStr.IndexOf("=") > 0)
                {
                    zqj = queryStr.Replace("zqj", "").Replace("=", "").Replace("(", "").Replace(")", "").Replace("'", "");
                    strWhere = "   CONVERT(nvarchar(10), datepart(yy,WaitDeliveryTime))  + '-'+ CONVERT(nvarchar(10),datepart(mm,WaitDeliveryTime))" + queryStr.Replace("zqj", "").Replace("(", "").Replace(")", "");
                }
                if (queryStr.IndexOf(">") > 0)
                {
                    zqj = queryStr.Replace("zqj", "").Replace(">", "").Replace("(", "").Replace(")", "").Replace("'", "");
                    strWhere = "   CONVERT(nvarchar(10), datepart(yy,WaitDeliveryTime))+ '-' + CONVERT(nvarchar(10),datepart(mm,WaitDeliveryTime))" + queryStr.Replace("zqj", "").Replace("(", "").Replace(")", "");
                }
                if (queryStr.IndexOf("<") > 0)
                {
                    zqj = queryStr.Replace("zqj", "").Replace("<", "").Replace("(", "").Replace(")", "").Replace("'", "");
                    strWhere = "    CONVERT(nvarchar(10),datepart(yy,WaitDeliveryTime)) + '-' + CONVERT(nvarchar(10),datepart(mm,WaitDeliveryTime))" + queryStr.Replace("zqj", "").Replace("(", "").Replace(")", "");
                }
                if (queryStr.IndexOf(">=") > 0)
                {
                    zqj = queryStr.Replace("zqj", "").Replace(">=", "").Replace("(", "").Replace(")", "").Replace("'", "");
                    strWhere = "    CONVERT(nvarchar(10),datepart(yy,WaitDeliveryTime)) + '-' + CONVERT(nvarchar(10), datepart(mm,WaitDeliveryTime))" + queryStr.Replace("zqj", "").Replace("(", "").Replace(")", "");
                }
                if (queryStr.IndexOf("<=") > 0)
                {
                    zqj = queryStr.Replace("zqj", "").Replace("<=", "").Replace("(", "").Replace(")", "").Replace("'", "");
                    strWhere = "    CONVERT(nvarchar(10),datepart(yy,WaitDeliveryTime))  + '-' + CONVERT(nvarchar(10),datepart(mm,WaitDeliveryTime))" + queryStr.Replace("zqj", "").Replace("(", "").Replace(")", "");
                }
                if (queryStr.IndexOf("between") > 0)
                {
                    arraystring = queryStr.Split(new string[] { "zqj", "between", "and" }, StringSplitOptions.None);
                    startTime = Convert.ToDateTime(arraystring[2].Replace("'", "") + "-01 00:00:00");
                    endTime = Convert.ToDateTime(arraystring[3].Replace("'", "") + "-01 00:00:00");
                    zqj = startTime.ToString("yyyyMM") + "-" + endTime.ToString("yyyyMM");
                    strWhere = "WaitDeliveryTime between  '" + startTime + "' and '" + endTime + "'";
                }
            }
            e.StrSql = @"select distinct CarTeamName, '{0}' as zqj
	            ,(case when qjncccs = 0.000  then 0
	               when qjncccs = 0 then 0
	               else Cast(((qjnyssl * 1.0 / 10000) / qjncccs) As Dec(20, 4)) end) '单车运载率'
	            ,(case when (case when qjnyslcs = 0.000 then 0
	                when qjnyslcs = 0 then 0
	                else CAST((qjnyyss * 1.0 / qjnyslcs * 100)  As Dec(20, 4)) end )=0.0000
	               then 0
	               else 
	                (case when qjnyslcs = 0.000 then 0
	                when qjnyslcs = 0 then 0
	                else CAST((qjnyyss * 1.0 / qjnyslcs * 100)  As Dec(20, 4)) end ) end) '百公里耗油'
	            ,
	               (case when qjnyssl = 0.0000 then 0
	                when qjnyssl = 0 then 0
                    when (CAST((qjnyssl * 1.0 / 10000)As Dec(20, 4)))=0.0000 then 0
	                else CAST((qjnyslcs * 1.0 / (CAST((qjnyssl * 1.0 / 10000)As Dec(20, 4)))) As Dec(20, 4))   end) '万只鸡里程' 
	         from  ( select CarTeamName,sum(qjnyssl) qjnyssl
	            ,sum(qjncccs) qjncccs
	            ,sum(qjnyyss) qjnyyss
	            ,sum(qjnyslcs) qjnyslcs
                 from (                    
                 select  CarTeamName
                ,(select (case when CONVERT(int, SUM(BuyChickCount))*0.0001 IS null then 0 else CONVERT(int, SUM(BuyChickCount))*0.0001 end) from dbo.YW_OrderInfo w inner join (select Id,OrderChildId,OrderId,BuyChickCount from dbo.YW_OrderDetail)  v on w.OrderCode=v.OrderId where {1} and OrderCode in  (select OrderId from dbo.YW_TaskCarRelation where REPLACE(CarNumber,' ','')=REPLACE(a.CarNumber,' ','')))qjnyssl
               ,(select count(0) from dbo.YW_TaskInfo where TaskId in ( select TaskId from dbo.YW_TaskCarRelation where REPLACE(CarNumber,' ','')=REPLACE(a.CarNumber,' ','') and OrderId in (select OrderCode from dbo.YW_OrderInfo where {2})))qjncccs
               ,(select Convert(int,isnull(sum(OilUse),0)) from dbo.YW_CostReportBase where REPLACE(CarNumber,' ','')=REPLACE(a.CarNumber,' ','')  and TaskId in (select TaskId from dbo.YW_TaskOrderRelation where OrderId in (select OrderCode from dbo.YW_OrderInfo where {3}))) qjnyyss
               ,(select Convert(int,isnull(sum(Distance),0)) from dbo.YW_CostReportBase where REPLACE(CarNumber,' ','')=REPLACE(a.CarNumber,' ','')  and TaskId in (select TaskId from dbo.YW_TaskOrderRelation where OrderId in (select OrderCode from dbo.YW_OrderInfo where {4})))qjnyslcs
                 from dbo.BD_CarInfo a )ww where qjnyssl<>0.0000 or qjncccs<>0 or qjnyyss<>0 or qjnyslcs<>0 group by CarTeamName)vvv";
            e.StrSql = string.Format(e.StrSql, zqj, strWhere, strWhere, strWhere, strWhere);
            return true;
        }
    
    }
}
